package com.manager.plat.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.apache.log4j.Logger;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;

import com.manager.plat.dao.IPartnerLowerDao;
import com.manager.plat.util.BaseUtil;
import com.manager.plat.vo.PartnerLowerVo;

public class PartnerLowerDaoImpl extends JdbcDaoSupport implements IPartnerLowerDao {
	private static Logger logger = Logger.getLogger(PartnerLowerDaoImpl.class);
	
	/**查询所有渠道信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<PartnerLowerVo> getPartnerLowerList() {
		String sql = "select pl.id, pl.name, pl.detail, pl.user_id, ui.login_username user_name " +
					 "from tb_ivr_partner_lower pl " +
					 "inner join tb_ivr_user_info ui on ui.id = pl.user_id and ui.status = 1 " +
					 "where pl.status = 1 " +
					 "order by pl.id desc";
		logger.info(String.format("查询所有有效渠道信息列表[%s]", sql));
		List<PartnerLowerVo> partnerLowerVoList = this.getJdbcTemplate().query(sql, rowMapper);
		if(partnerLowerVoList != null && partnerLowerVoList.size() > 0){
			return partnerLowerVoList;
		}
		
		return null;
	}
	/**查询所有渠道信息列表*/
	@SuppressWarnings("unchecked")
	@Override
	public List<PartnerLowerVo> getPartnerLowerListAll() {
		String sql = "select pl.id, pl.name, pl.detail, pl.user_id, ui.login_username user_name " +
				 "from tb_ivr_partner_lower pl " +
				 "inner join tb_ivr_user_info ui on ui.id = pl.user_id " +
				 "order by pl.id desc";
	logger.info(String.format("查询所有有效渠道信息列表[%s]", sql));
	List<PartnerLowerVo> partnerLowerVoList = this.getJdbcTemplate().query(sql, rowMapper);
	if(partnerLowerVoList != null && partnerLowerVoList.size() > 0){
		return partnerLowerVoList;
	}
	
	return null;
	}
	/**根据Id获得渠道信息*/
	@SuppressWarnings("unchecked")
	@Override
	public PartnerLowerVo getPartnerLowerById(int id) {
		String sql = "select pl.id, pl.name, pl.detail, pl.user_id, ui.login_username user_name " +
				 "from tb_ivr_partner_lower pl " +
				 "inner join tb_ivr_user_info ui on ui.id = pl.user_id and ui.status = 1 " +
				 "where pl.id = ? and pl.status = 1 " +
				 "order by pl.id desc";
		
		Object[] param = new Object[]{id};
		logger.info(String.format("根据Id获得渠道信息[%s]", BaseUtil.logSQL(sql, param)));
		
		List<PartnerLowerVo> partnerLowerVoList = this.getJdbcTemplate().query(sql, param, rowMapper);
		if(partnerLowerVoList != null && partnerLowerVoList.size() > 0){
			return partnerLowerVoList.get(0);
		}
		
		return null;
	}
	/**数据转换*/
	private ParameterizedRowMapper<PartnerLowerVo> rowMapper = new ParameterizedRowMapper<PartnerLowerVo>(){
		@Override
		public PartnerLowerVo mapRow(ResultSet rs, int rowNum) throws SQLException {
			PartnerLowerVo partnerLowerVo = new PartnerLowerVo();
			partnerLowerVo.setId(rs.getInt("id"));
			partnerLowerVo.setName(rs.getString("name"));
			partnerLowerVo.setDetail(rs.getString("detail"));
			partnerLowerVo.setUser_id(rs.getString("user_id"));
			partnerLowerVo.setUser_name(rs.getString("user_name"));
			return partnerLowerVo;
		}
	};
	/**保存渠道信息*/
	@Override
	public int insertPartnerLower(PartnerLowerVo partnerLowerVo) {
		String sql = "insert into tb_ivr_partner_lower(name,detail,user_id,status,add_user) values(?,?,?,1,?)";
		Object[] param = new Object[]{partnerLowerVo.getName(),partnerLowerVo.getDetail(),
									partnerLowerVo.getUser_id(),partnerLowerVo.getAdd_user()};
		logger.info(String.format("保存渠道信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**更新渠道信息*/
	@Override
	public int updatePartnerLower(PartnerLowerVo partnerLowerVo) {
		String sql = "update tb_ivr_partner_lower set name = ?, detail = ?, user_id = ?, update_date = now(), update_user = ? where id = ?";
		Object[] param = new Object[]{partnerLowerVo.getName(),partnerLowerVo.getDetail(),
									partnerLowerVo.getUser_id(),partnerLowerVo.getUpdate_user(),
									partnerLowerVo.getId()};
		logger.info(String.format("更新渠道信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		
		return count;
	}
	/**删除渠道信息*/
	@Override
	public int deletePartnerLower(int id,int update_user) {
		String sql = "update tb_ivr_partner_lower set status = 0, update_date = now(), update_user = ? where id = ?";
		Object[] param = new Object[]{update_user, id};
		logger.info(String.format("删除渠道信息[%s]", BaseUtil.logSQL(sql, param)));
		
		int count = this.getJdbcTemplate().update(sql, param);
		return count;
	}
	/**根据渠道ID获得该渠道下所有的长号码*/
	@SuppressWarnings("unchecked")
	@Override
	public List<String> getPartnerLowerSpNumberById(int lower_id) {
		String sql = "select r.sp_number " +
					 "from tb_ivr_partner_lower pl " +
					 "inner join tb_ivr_router r on r.lower_id = pl.id and r.status = 1 " +
					 "where r.lower_id = ? and  pl.status = 1";
		Object[] param = new Object[]{lower_id};
		logger.info(String.format("根据渠道ID获得该渠道下所有的长号码[%s]", BaseUtil.logSQL(sql, param)));
		
		List<String> sp_numberList = this.getJdbcTemplate().query(sql, param, tempMapper);
		if(sp_numberList != null && sp_numberList.size() > 0){
			return sp_numberList;
		}
		
		return null;
	}
	
	/**数据转换*/
	private ParameterizedRowMapper<String> tempMapper = new ParameterizedRowMapper<String>(){
		@Override
		public String mapRow(ResultSet rs, int rowNum) throws SQLException {
			String sp_number = rs.getString("sp_number");
			return sp_number;
		}
	};
	
}